package com.eryos.android.cigarettecounter.database;

import java.util.ArrayList;
import java.util.TreeMap;

import android.database.Cursor;
import android.util.Log;

import com.eryos.android.cigarettecounter.beans.SmokeAverage;
import com.eryos.android.cigarettecounter.beans.SmokeCounter;

/**
 * Project :  CigaretteCounter
 * Author :   Olivier Combe 
 * URL :      http://eryos.fr/CigaretteCounter/
 *
 * Copyright (C) 2012 - Olivier Combe 
 * 
 * Licensed under the Apache License, Version 2.0 (the "License"); you may not
 * use this file except in compliance with the License. You may obtain a copy of
 * the License at
 * 
 * http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
 * License for the specific language governing permissions and limitations under
 * the License.
 *
 */

public class WeeklyAdapterImpl implements ExtendedPeriodicAdapterInterface {

	private CigaretteDatabase currentDatabase;
	
	public WeeklyAdapterImpl(CigaretteDatabase db){
		currentDatabase = db;
	}
	
	/**
	 * Return the number of cigarettes smoked this week
	 * @return the number of cigarettes smoked this week
	 */
	public int getCount(){
		Log.i(this.getClass().getSimpleName(), "get Weekly Count");
		String sqlQuery = 
			"SELECT   count(1) Nb "+
			"FROM     "+ DBConnector.DATABASE_TABLE +" "+
			"WHERE    "+DBFields.DB_WEEK+" = "+DBFields.THIS_WEEK+"; ";

		Cursor mCursor = currentDatabase.executeQuery(sqlQuery);

		return DBTools.getFirstIntResult(mCursor);
	}

	
	public int getCountNew(){
		Log.i(this.getClass().getSimpleName(), "get Weekly Count");

		SQLQuery sql = new SQLQuery();
		sql.setSelect(DBFields.COUNT);
		sql.setWhere(DBFields.FILTER_THIS_WHEEK);

		Cursor mCursor = currentDatabase.executeQuery(sql.toString());

		return DBTools.getFirstIntResult(mCursor);
	}

	
	/**
	 * Return the number of cigarettes smoked this week
	 */
	public double getCost(){
		Log.i(this.getClass().getSimpleName(), "get Weekly Cost");
		String sqlQuery = 
			"SELECT   round(sum(ifnull(smoke_valid,0)), "+DBTools.ROUNDING+") Nb "+
			"FROM     "+ DBConnector.DATABASE_TABLE +" "+
			"WHERE    strftime('%W', smoke_time) = "+DBFields.THIS_WEEK+"; ";

		Cursor mCursor = currentDatabase.executeQuery(sqlQuery);

		return DBTools.getFirstDoubleResult(mCursor);
	}

	/**
	 * Return the average number of cigarettes smoked by day this week
	 */
	public double getAverage(){
		Log.i(this.getClass().getSimpleName(), "get Weekly Average");
		try {
			String sqlQuery = 
				"SELECT sum(nb), count(nb), round(avg(nb), "+DBTools.ROUNDING+") "+
				"FROM  "+
				"( "+
				"    SELECT   "+DBFields.DATE+" Day, "+DBFields.COUNT+
				"    FROM     "+ DBConnector.DATABASE_TABLE +" "+
				"    WHERE    strftime('%W', smoke_time) = "+DBFields.THIS_WEEK+" "+
				"    GROUP BY "+DBFields.DATE+
				"    ORDER BY "+DBFields.DATE+
				");";

			double res = -1;

			Cursor mCursor = currentDatabase.executeQuery(sqlQuery);

			if (mCursor != null) {
				mCursor.moveToFirst();
				res =  mCursor.getDouble(2);
			}

			return res;
		} catch (Exception e) {
			e.printStackTrace();
			return -1;
		}
	}

	
	/**
	 * Return the number of cigarettes smoked each day of the week
	 */
	public TreeMap<Integer, SmokeCounter> getDetails(){
		Log.i(this.getClass().getSimpleName(), "get Weekly Details");
		String sqlQuery =
			//"SELECT   strftime('%w', smoke_time) Day, count(1) Nb  " +
			"SELECT   strftime('%w', smoke_time) Day, count(1) Nb, strftime('%d', smoke_time) Day, strftime('%m', smoke_time) Month, strftime('%Y', smoke_time) Year  "+
			"FROM     "+ DBConnector.DATABASE_TABLE +" " +
			"WHERE    strftime('%W', smoke_time) = "+DBFields.THIS_WEEK+" " +
			"GROUP BY strftime('%w', smoke_time) " +
			"ORDER BY strftime('%Y-%m-%d', smoke_time); ";

		Cursor mCursor = currentDatabase.executeQuery(sqlQuery);

		//return DBTools.parseCursorAsSmokeCounter(mCursor);
		return DBTools.parseCursorAsSmokeCounterSet(mCursor);
	}
	
	/**
	 * Return the number of cigarettes smoked each day of the week
	 */
	public TreeMap<Integer, SmokeCounter> getPreviousDetails(){
		Log.i(this.getClass().getSimpleName(), "get Weekly Details");
		String sqlQuery =
			//"SELECT   strftime('%w', smoke_time) Day, count(1) Nb  " +
			"SELECT   strftime('%w', smoke_time) Day, count(1) Nb, strftime('%d', smoke_time) Day, strftime('%m', smoke_time) Month, strftime('%Y', smoke_time) Year  "+
			"FROM     "+ DBConnector.DATABASE_TABLE +" " +
			"WHERE    strftime('%W', smoke_time) = "+DBFields.PREVIOUS_WEEK+" " +
			"GROUP BY strftime('%w', smoke_time) " +
			"ORDER BY strftime('%Y-%m-%d', smoke_time); ";

		Cursor mCursor = currentDatabase.executeQuery(sqlQuery);

		//return DBTools.parseCursorAsSmokeCounter(mCursor);
		return DBTools.parseCursorAsSmokeCounterSet(mCursor);
	}

	/**
	 * Return the average number of cigarettes smoked by day for the last 6 weeks
	 */
	public ArrayList<SmokeAverage> getHistoryAverage(){
		Log.i(this.getClass().getSimpleName(), "get Last 6 Weekly History");
		String sqlQuery = 
			"SELECT Week, sum(nb), count(nb), round(avg(nb), "+DBTools.ROUNDING+") "+
			"FROM  "+
			"( "+
			"    SELECT   strftime('%W', smoke_time) Week, strftime('%Y-%m-%d', smoke_time) Day, count(1) Nb  "+
			"    FROM     "+ DBConnector.DATABASE_TABLE +" "+
			"    WHERE    strftime('%Y-%W', smoke_time) > "+DBFields.SIX_WEEKS_AGO+" "+
			"    GROUP BY strftime('%W', smoke_time), strftime('%d/%m/%Y', smoke_time) "+ 
			") "+
			"GROUP BY Week " + 
			"ORDER BY Day; ";

		Cursor mCursor = currentDatabase.executeQuery(sqlQuery);

		return DBTools.parseCursorAsSmokeAverage(mCursor);
	}
	
	/**
	 * Return the average number of cigarettes smoked by day for the last 6 month
	 */
	public ArrayList<SmokeAverage> getHistoryCost(){
		Log.i(this.getClass().getSimpleName(), "get Last 6 Months Costs");
		try {
			String sqlQuery = 
				"SELECT Month, sum(cost), count(cost), round(sum(ifnull(cost,0)), "+DBTools.ROUNDING+") "+
				"FROM "+ 
				"( "+ 
				"    SELECT   strftime('%m', smoke_time) Month, strftime('%Y-%m-%d', smoke_time) Day, sum(smoke_valid) cost  "+
				"    FROM     "+ DBConnector.DATABASE_TABLE +" "+
				"    WHERE    strftime('%Y-%W', smoke_time) > "+DBFields.SIX_WEEKS_AGO+" "+
				"    GROUP BY strftime('%m', smoke_time), strftime('%d/%m/%Y', smoke_time) ORDER BY smoke_time"+
				") "+
				"GROUP BY Month " +
				"ORDER BY Day; ";

			Cursor mCursor = currentDatabase.executeQuery(sqlQuery);

			return DBTools.parseCursorAsSmokeAverage(mCursor);
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

}
